import 'package:drift/drift.dart';
import 'package:drift_flutter/drift_flutter.dart';
import 'package:path_provider/path_provider.dart';

part 'database.g.dart';


class Notes extends Table {
  IntColumn get id => integer().autoIncrement()(); // 主键，自增
  TextColumn get content => text().withLength(min: 0, max: 10000)(); // 文字内容
  DateTimeColumn get createdAt => dateTime().clientDefault(() => DateTime.now())();
  DateTimeColumn get updatedAt => dateTime().nullable()();
}

class NoteImages extends Table {
  IntColumn get id => integer().autoIncrement()();
  IntColumn get noteId => integer().customConstraint('REFERENCES notes(id) ON DELETE CASCADE')();
  TextColumn get imagePath => text()(); // 本地图片路径
}


@DriftDatabase(tables: [Notes,NoteImages])
class AppDatabase extends _$AppDatabase {
  // After generating code, this class needs to define a `schemaVersion` getter
  // and a constructor telling drift where the database should be stored.
  // These are described in the getting started guide: https://drift.simonbinder.eu/setup/
  AppDatabase([QueryExecutor? executor]) : super(executor ?? _openConnection());

  Future<List<Note>> getAllNotesOrderByDate() {
    return (select(notes)
      ..orderBy([
            (t) => OrderingTerm(expression: t.createdAt, mode: OrderingMode.desc),
      ]))
        .get();

  }
  Future<int> addNote(String content) {
    return into(notes).insert(
      NotesCompanion(
        content: Value(content),
        // createdAt: Value(DateTime.now()), // 不写也会自动填充
      ),
    );
  }
  Future<int> deleteNoteById(int id) {
    return (delete(notes)..where((tbl) => tbl.id.equals(id))).go();
  }

  Future<int> updateNoteContent(int id, String newContent) {
    return (update(notes)..where((tbl) => tbl.id.equals(id))).write(
      NotesCompanion(
        content: Value(newContent),
        updatedAt: Value(DateTime.now()),
      ),
    );
  }



  @override
  int get schemaVersion => 1;

  static QueryExecutor _openConnection() {
    return driftDatabase(
      name: 'my_database',
      native: const DriftNativeOptions(
        // By default, `driftDatabase` from `package:drift_flutter` stores the
        // database files in `getApplicationDocumentsDirectory()`.
        databaseDirectory: getApplicationSupportDirectory,
      ),
      // If you need web support, see https://drift.simonbinder.eu/platforms/web/
    );
  }
}